-- =============================================
-- Author:        Paul Bradshaw
-- Create date: Sept 19, 2006
-- Description:    Get DB Version string
-- =============================================
CREATE PROCEDURE [dbo].[asi_GetDatabaseVersion]
    @p1 nvarchar(20) = '' -- Specify which version part, or nothing for full version
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @version nvarchar(24)
    DECLARE @major nvarchar(10)
    DECLARE @minor nvarchar(10)
    DECLARE @patch nvarchar(10)
    DECLARE @build nvarchar(10)
    DECLARE @systemEntity uniqueidentifier
    DECLARE @type nvarchar(20)

    SET @type = UPPER(@p1)

    SELECT @systemEntity = SystemEntityKey FROM SystemEntity WHERE SystemKeyword = 'Organization'

    SELECT @major = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Major'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @minor = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Minor'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @patch = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Patch'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @build = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Build'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity

    IF @major IS NOT NULL AND @minor IS NOT NULL AND @patch IS NOT NULL AND @build IS NOT NULL
    BEGIN
        IF @type = ''
        BEGIN
            SET @version = @major + '.' + @minor + '.' + @patch + '.' + @build
        END
        ELSE IF @type = 'NOBUILD'
        BEGIN
            SET @version = @major + '.' + @minor + '.' + @patch
        END
        ELSE IF @type = 'MAJORMINOR'
        BEGIN
            SET @version = @major + '.' + @minor
        END
        ELSE IF @type = 'MAJOR'
        BEGIN
            SET @version = @major
        END
        ELSE IF @type = 'MINOR'
        BEGIN
            SET @version = @minor
        END
        ELSE IF @type = 'PATCH'
        BEGIN
            SET @version = @patch
        END
        ELSE IF @type = 'BUILD'
        BEGIN
            SET @version = @build
        END
        
    END
    ELSE
    BEGIN
        SET @version = NULL
    END

    SELECT @version

END

GO